package com.gopay.common.base.dao.impl;

import java.io.Serializable;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.annotation.Resource;

import oracle.jdbc.OracleCallableStatement;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Projections;
import org.hibernate.transform.Transformers;
import org.hibernate.type.NullableType;
import org.springframework.dao.DataAccessResourceFailureException;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.orm.hibernate3.SessionFactoryUtils;

import com.gopay.common.base.dao.BaseReadDAO;

/**
 * 查询基础类
 * 
 * @BaseReadDAOImpl.java
 * @2013-3-1 上午10:33:02 www.gopay.com.cn Inc.All rights reserved.
 */
@SuppressWarnings("all")
public class BaseReadDAOImpl<T extends Serializable, Pk extends Serializable> implements BaseReadDAO<T, Pk> {

    @Resource(name = "hibernateTemplate")
    private HibernateTemplate hibernateTemplate;

    public HibernateTemplate getHibernateTemplate() {
        return hibernateTemplate;
    }

    public List<T> findByExample(T entity) {
        return getHibernateTemplate().findByExample(entity);
    }

    public List<T> findAll(Class<T> entityClass) {
        return getHibernateTemplate().loadAll(entityClass);
    }

    public T find(Class<T> entity, Pk id) {
        return (T) getHibernateTemplate().get(entity, id);
    }

    public List<T> findByCriteria(DetachedCriteria criteria) {
        return getHibernateTemplate().findByCriteria(criteria);
    }

    public List<T> findBySql(final String sql) {
        return findBySql(sql, null);
    }

    public List<T> findBySqlScalar(final String sql, final Set<String> scalarSet) {
        return (List<T>) getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                SQLQuery query = session.createSQLQuery(sql);
                for (String scalar : scalarSet) {
                    query.addScalar(scalar);
                }
                List<T> list = query.list();
                return list;
            }
        });
    }

    public List<T> findBySql(final String sql, final Map<String, Object> param) {
        return (List<T>) getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(sql);
                if (param != null) {
                    Iterator<String> it = param.keySet().iterator();
                    while (it.hasNext()) {
                        String key = it.next();
                        Object value = param.get(key);
                        if (value instanceof Object[]) {
                            query.setParameterList(key, (Object[]) value);
                        } else {
//                            query.setString(key, param.get(key).toString());
                        	query.setParameter(key, param.get(key));
                        }
                    }
                }
                return query.list();
            }
        });
    }

    public List<T> findByHql(final String hql) {
        return findByHql(hql, null);
    }

    public List<T> findByHql(final String hql, final Map<String, Object> param) {
        return (List<T>) getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createQuery(hql);
                if (param != null) {
                    Iterator<String> it = param.keySet().iterator();
                    while (it.hasNext()) {
                        String key = it.next();
                        Object value = param.get(key);
                        if (value instanceof List) {
                        	query.setParameterList(key, (List) value);
                        } else if (value instanceof Object[]) {
                            query.setParameterList(key, (Object[]) value);
                        } else {
                        	query.setParameter(key, param.get(key));
                        }
                    }
                }
                return query.list();
            }
        });
    }

    public List<T> findByHql(final String hql, final Map<String, Object> param, final int pageIndex, final int pageSize) {
        return (List<T>) getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createQuery(hql);
                if (param != null) {
                    Iterator<String> it = param.keySet().iterator();
                    while (it.hasNext()) {
                        String key = it.next();
                        Object value = param.get(key);
                        if (value instanceof Object[]) {
                            query.setParameterList(key, (Object[]) value);
                        } else {
//                            query.setString(key, param.get(key).toString());
                            query.setParameter(key, param.get(key));
                        }
                    }
                }
                if (pageIndex >= 1) {
                    query.setFirstResult((pageIndex - 1) * pageSize);
                    query.setMaxResults(pageSize);
                }
                return query.list();
            }
        });
    }

    public List<T> findByCriteria(final DetachedCriteria criteria, final int pageNo, final int pageSize) {
        return (List<T>) getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Criteria c = criteria.getExecutableCriteria(session);
                if (pageNo >= 1) {
                    return c.setFirstResult((pageNo - 1) * pageSize).setMaxResults(pageSize).list();
                }
                return c.list();
            }
        });
    }

    public int findCountByCriteria(final DetachedCriteria criteria) {
        return (Integer) getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Criteria c = criteria.getExecutableCriteria(session);
                return c.setProjection(Projections.rowCount()).uniqueResult();
            }
        });
    }

    public List<T> findBySql(final String sql, final int pageNo, final int pageSize) {
        return (List<T>) getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(sql);
                if (pageNo >= 1) {
                    query.setFirstResult((pageNo - 1) * pageSize);
                    query.setMaxResults(pageSize);
                }
                List<T> list = query.list();
                return list;
            }
        });
    }

    public List<T> findBySql(final String sql, final Map<String, Object> param, final int pageNo, final int pageSize) {
        return (List<T>) getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(sql);
                if (param != null) {
                    Iterator<String> it = param.keySet().iterator();
                    while (it.hasNext()) {
                        String key = it.next();
                        Object value = param.get(key);
                        if (value instanceof Object[]) {
                            query.setParameterList(key, (Object[]) value);
                        } else {
//                            query.setString(key, param.get(key).toString());
                            query.setParameter(key, param.get(key));
                        }
                    }
                }
                if (pageNo >= 1) {
                    query.setFirstResult((pageNo - 1) * pageSize);
                    query.setMaxResults(pageSize);
                }
                List<T> list = query.list();
                return list;
            }
        });
    }

    public List<T> findBySqlScalar(final String sql, final int pageNo, final int pageSize, final Set<String> scalarSet) {
        return (List<T>) getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                SQLQuery query = session.createSQLQuery(sql);
                for (String scalar : scalarSet) {
                    query.addScalar(scalar);
                }
                if (pageNo >= 1) {
                    query.setFirstResult((pageNo - 1) * pageSize);
                    query.setMaxResults(pageSize);
                }
                List<T> list = query.list();
                return list;
            }
        });
    }

    public int findCountBySql(final String sql) {
        return (Integer) getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(sql);
                List<T> list = query.list();
                return Integer.parseInt(list.get(0).toString());
            }
        });
    }

    @Override
    public int findCountByHql(final String hql, final Map<String, Object> param) {
        return (int) getHibernateTemplate().execute(new HibernateCallback<Integer>() {
            public Integer doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createQuery(hql);
                if (param != null) {
                    Iterator<String> it = param.keySet().iterator();
                    while (it.hasNext()) {
                        String key = it.next();
                        Object value = param.get(key);
                        if (value instanceof List) {
                            query.setParameterList(key, (List) value);
                        } else if (value instanceof Object[]) {
                            query.setParameterList(key, (Object[]) value);
                        } else {
                            query.setParameter(key, param.get(key));
                        }
                    }
                }
                List list = query.list();
                if(list.size() == 0) {
                    return 0;
                }
                return ((Number) list.get(0)).intValue();
            }
        });
    }

    @Override
    public int findCountBySql(String sql, final Map<String, Object> param) {
        sql = buildCountSql(sql);
        
        return findCountBySqlOfHibernate(sql,param);
    }

    /**
     * 根据SQL语句查询记录总数之hibernate版
     * 
     * @param sql
     * @param param
     * @return
     */
    public int findCountBySqlOfHibernate(final String sql, final Map<String, Object> param) {
        return (Integer) getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(sql);
                if (param != null) {
                    Iterator<String> it = param.keySet().iterator();
                    while (it.hasNext()) {
                        String key = it.next();
                        Object value = param.get(key);
                        if (value instanceof Object[]) {
                            query.setParameterList(key, (Object[]) value);
                        } else {
//                            query.setString(key, param.get(key).toString());
                        	query.setParameter(key, param.get(key));
                        }
                    }
                }
                List<T> list = query.list();
                return Integer.parseInt(list.get(0).toString());
            }
        });
    }

    private Query buildSqlQuery(Session session, String hql, Map<String, Object> params, int startIndex, int pageSize) {
        Query query = session.createSQLQuery(hql);
        /* 增加动态参数 */
        if (params != null) {
            for (Map.Entry<String, Object> entry : params.entrySet()) {
                query.setParameter(entry.getKey(), entry.getValue());
            }
        }
        /* 增加分页 */
        if (startIndex >= 0) {
            query.setFirstResult(startIndex);
            query.setMaxResults(pageSize);
        }
        return query;
    }

    private Query buildHqlQuery(Session session, String hql, Map<String, Object> params, int startIndex, int pageSize) {
        Query query = session.createQuery(hql);
        /* 增加动态参数 */
        if (params != null) {
            for (Map.Entry<String, Object> entry : params.entrySet()) {
                query.setParameter(entry.getKey(), entry.getValue());
            }
        }
        /* 增加分页 */
        if (startIndex >= 0) {
            query.setFirstResult(startIndex);
            query.setMaxResults(pageSize);
        }
        return query;
    }

    private String buildCountSql(String sql) {
        int fromIndex = sql.toLowerCase().indexOf("from");
        if (fromIndex == -1) {
            throw new IllegalArgumentException("Does not recognize this sql statement :" + sql);
        }
        sql = "select count(*) " + sql.substring(fromIndex);
        return sql;
    }

    private SessionFactory getSessionFactory() {
        return (this.hibernateTemplate != null ? this.hibernateTemplate.getSessionFactory() : null);
    }

    protected Session getSession(boolean allowCreate) throws DataAccessResourceFailureException, IllegalStateException {

        return (!allowCreate ? SessionFactoryUtils.getSession(getSessionFactory(), false) : SessionFactoryUtils
                .getSession(getSessionFactory(), this.hibernateTemplate.getEntityInterceptor(),
                        this.hibernateTemplate.getJdbcExceptionTranslator()));
    }

    protected void releaseSession(Session session) {
        SessionFactoryUtils.releaseSession(session, getSessionFactory());
    }
    
    public List<T> findBySqlScalar(final String sql, final int pageNo, final int pageSize,
            final Map<String, NullableType> scalarMap, final Class<T> target, final Map<String, Object> param) {
        return (List<T>) getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                SQLQuery query = session.createSQLQuery(sql);
                for (Map.Entry<String, NullableType> m : scalarMap.entrySet()) {
                    query.addScalar(m.getKey(), m.getValue());
                }
                if (pageNo >= 1) {
                    query.setFirstResult((pageNo - 1) * pageSize);
                    query.setMaxResults(pageSize);
                }
                query.setResultTransformer(Transformers.aliasToBean(target));
                // 将所查出来的数据设置为hashMap类型
                // query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
                if (param != null && param.size() != 0) {
                    Iterator<String> it = param.keySet().iterator();
                    while (it.hasNext()) {
                        String key = it.next();
                        Object value = param.get(key);
                        if (value instanceof Object[]) {
                            query.setParameterList(key, (Object[]) value);
                        } else {
//                            query.setString(key, param.get(key).toString());
                        	query.setParameter(key, param.get(key));
                        }
                    }
                }
                List<T> list = (List<T>) query.list();
                return list;
            }
        });
    }

    @Override
    public T findBySqlScalar(final String sql, final Map<String, NullableType> scalarMap, final Class<T> target,
            final Map<String, Object> param) {
        return (T) getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                SQLQuery query = session.createSQLQuery(sql);
                for (Map.Entry<String, NullableType> m : scalarMap.entrySet()) {
                    query.addScalar(m.getKey(), m.getValue());
                }
                query.setResultTransformer(Transformers.aliasToBean(target));
                if (param != null && param.size() != 0) {
                    Iterator<String> it = param.keySet().iterator();
                    while (it.hasNext()) {
                        String key = it.next();
                        Object value = param.get(key);
                        if (value instanceof Object[]) {
                            query.setParameterList(key, (Object[]) value);
                        } else {
//                            query.setString(key, param.get(key).toString());
                        	query.setParameter(key, param.get(key));
                        }
                    }
                }
                List<T> list = (List<T>) query.list();
                return list == null || list.size() == 0 ? null : list.get(0);
            }
        });
    }

    public String getNextId(final String sequence) {
        List<Object> data = this.getHibernateTemplate().executeFind(new HibernateCallback<Object>() {
            @Override
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                return session.createSQLQuery("select " + sequence + ".nextval from dual").list();
            }
        });
        
        if(data==null || data.size()==0) {
            throw new IllegalStateException(" getNextId return data is null");
        }
        return data.get(0).toString();
    }
    public String getNextIdFillZero(final String sequence, final int bit) {
        List<Object> data = this.getHibernateTemplate().executeFind(new HibernateCallback<Object>() {
            @Override
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                return session.createSQLQuery(
                        "select replace(lpad(" + sequence + ".nextval," + bit + "),' ','0') from dual").list();
            }
        });
        
        if(data==null || data.size()==0) {
            throw new IllegalStateException(" getNextIdFillZero return data is null");
        }
        return data.get(0).toString();
    }
    
    public List<Object> executeProcedure(final String procedureName, final List<Object> inputList,final List<Integer> outTypeList) {
    	Object objList  = null;
    	if(StringUtils.isBlank(procedureName)) {
            throw new IllegalStateException(" procedure is invalid");
        }
    	try {
    		int paramLength = 0;
    		if(inputList != null) {
                paramLength += inputList.size();
            }
    		if(outTypeList != null) {
                paramLength += outTypeList.size();
            }
	    	final String executeProcedure = getProcedureSql(procedureName, paramLength);
	    	
	    	objList = getHibernateTemplate().execute(new HibernateCallback(){
				@Override
				public Object doInHibernate(Session session) throws HibernateException, SQLException {
					Connection con = session.connection();
					CallableStatement cstmt = con.prepareCall(executeProcedure);
					
					for(int i=1;inputList!=null && i<inputList.size()+1 ; i++){
						cstmt.setObject(i, inputList.get(i-1));
					}
					int start = inputList==null?1:inputList.size()+1;
					for(int i=start,j=0;outTypeList!=null && i<outTypeList.size()+start; i++,j++){
						cstmt.registerOutParameter(i, outTypeList.get(j));
					}
					
					cstmt.execute();
					List<Object> list = new ArrayList<Object>();
					for(int i=start;outTypeList!=null && i<outTypeList.size()+start; i++){
						Object res = cstmt.getObject(i);
						if(res!=null) {
                            list.add(res);
                        }
					}
					return list;
				}
            	    	});
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		}
    	return (objList == null)? null : (List<Object>)objList;
    }
    
    private String getProcedureSql(String procedureName,int length){
    	StringBuilder sql = new StringBuilder();
		sql.append("{ call ");
		sql.append(StringUtils.trim(procedureName));
		if (length > 0) {
			sql.append("(");
			for (int i = 0; i < length; i++) {
				sql.append("?");
				if (i != length - 1) {
					sql.append(",");
				}
			}
			sql.append(")");
		}
		sql.append("}");
		return sql.toString();
    }

}
